package com.isecommerce.application.csvimporter.subdao;

import java.io.BufferedReader;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.*;

import com.isecommerce.framework.entity.*;
import com.isecommerce.framework.util.*;
import com.isecommerce.application.csvimporter.SubDAO;
import com.isecommerce.application.csvimporter.entity.CSVFileInfo;

public class WCS_ADDRESS implements SubDAO{
	
	private String SQL_STMT_I = 
		"INSERT INTO ADDRESS(" +
		"        ADDRESS_ID," +
		"        ADDRESSTYPE," +
		"        MEMBER_ID," +
		"        ADDRBOOK_ID," +
		"        ORGUNITNAME," +
		"        FIELD3," +
		"        BILLINGCODE," +
		"        BILLINGCODETYPE," +
		"        STATUS," +
		"        ORGNAME," +
		"        ISPRIMARY," +
		"        LASTNAME," +
		"        PERSONTITLE," +
		"        FIRSTNAME," +
		"        MIDDLENAME," +
		"        BUSINESSTITLE," +
		"        PHONE1," +
		"        FAX1," +
		"        PHONE2," +
		"        ADDRESS1," +
		"        FAX2," +
		"        NICKNAME," +
		"        ADDRESS2," +
		"        ADDRESS3," +
		"        CITY," +
		"        STATE," +
		"        COUNTRY," +
		"        ZIPCODE," +
		"        EMAIL1," +
		"        EMAIL2," +
		"        PHONE1TYPE," +
		"        PHONE2TYPE," +
		"        PUBLISHPHONE1," +
		"        PUBLISHPHONE2," +
		"        BESTCALLINGTIME," +
		"        PACKAGESUPPRESSION," +
		"        LASTCREATE," +
		"        OFFICEADDRESS," +
		"        SELFADDRESS," +
		"        FIELD1," +
		"        FIELD2," +
		"        TAXGEOCODE," +
		"        SHIPPINGGEOCODE," +
		"        MOBILEPHONE1," +
		"        MOBILEPHONE1CNTRY," +
		"        OPTCOUNTER" +
		"    )VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	
	public void executeImport(BufferedReader csvFile, Connection conn, CSVFileInfo fileInfo) throws IOException{
		// Temp entity
		String nextLine = "";		
		PreparedStatement pstmt = null;
		CSVEntity csvE = null;
		int totalCnt = 0;
		
		System.out.println("<SubDAO:" + this.getClass().getName() + ">\n");
		
		System.out.println("Start Importing ....\n");
		
		try{
			
			conn.setAutoCommit(false);
			
			// Read csv data by line
			while((nextLine = csvFile.readLine()) != null){        			
								
				System.out.println("[READING CSV ROW] -> " + (totalCnt+1));
				// for DEBUG: System.out.println(nextLine);
				
				// Generate CSV entity
				csvE = new CSVEntity(nextLine, fileInfo.coldel);
				// for DEBUG: System.out.println(csvE);
								
				// Execute prepared statement
				pstmt = conn.prepareStatement(SQL_STMT_I);
				
				pstmt.setBigDecimal(1, Utility.getBigDecimal(csvE.getItemAt(0))); 
                pstmt.setString(2, csvE.getItemAt(1));
                pstmt.setBigDecimal(3, Utility.getBigDecimal(csvE.getItemAt(2))); 
                pstmt.setBigDecimal(4, Utility.getBigDecimal(csvE.getItemAt(3))); 
                pstmt.setString(5, csvE.getItemAt(4));
                pstmt.setString(6, csvE.getItemAt(5));
                pstmt.setString(7, csvE.getItemAt(6));
                pstmt.setString(8, csvE.getItemAt(7));
                pstmt.setString(9, csvE.getItemAt(8));
                pstmt.setString(10, csvE.getItemAt(9));
                
                pstmt.setInt(11, Utility.getInteger(csvE.getItemAt(10))); 
                pstmt.setString(12, csvE.getItemAt(11));
                pstmt.setString(13, csvE.getItemAt(12));
                pstmt.setString(14, csvE.getItemAt(13));
                pstmt.setString(15, csvE.getItemAt(14));
                pstmt.setString(16, csvE.getItemAt(15));
                pstmt.setString(17, csvE.getItemAt(16));
                pstmt.setString(18, csvE.getItemAt(17));
                pstmt.setString(19, csvE.getItemAt(18));
                pstmt.setString(20, csvE.getItemAt(19));
                
                pstmt.setString(21, csvE.getItemAt(20));
                pstmt.setString(22, csvE.getItemAt(21));
                pstmt.setString(23, csvE.getItemAt(22));
                pstmt.setString(24, csvE.getItemAt(23));
                pstmt.setString(25, csvE.getItemAt(24));
                pstmt.setString(26, csvE.getItemAt(25));
                pstmt.setString(27, csvE.getItemAt(26));
                pstmt.setString(28, csvE.getItemAt(27));
                pstmt.setString(29, csvE.getItemAt(28));
                pstmt.setString(30, csvE.getItemAt(29));
                
                pstmt.setString(31, csvE.getItemAt(30));
                pstmt.setString(32, csvE.getItemAt(31));
                pstmt.setInt(33, Utility.getInteger(csvE.getItemAt(32))); 
                pstmt.setInt(34, Utility.getInteger(csvE.getItemAt(33))); 
                pstmt.setString(35, csvE.getItemAt(34));
                pstmt.setInt(36, Utility.getInteger(csvE.getItemAt(35))); 
                pstmt.setString(37, csvE.getItemAt(36));
                pstmt.setString(38, csvE.getItemAt(37));
                pstmt.setInt(39, Utility.getInteger(csvE.getItemAt(38))); 
                pstmt.setString(40, csvE.getItemAt(39));
                
                pstmt.setString(41, csvE.getItemAt(40));
                pstmt.setString(42, csvE.getItemAt(41));
                pstmt.setString(43, csvE.getItemAt(42));
                pstmt.setString(44, csvE.getItemAt(43));
                pstmt.setString(45, csvE.getItemAt(44));
                pstmt.setInt(46, Utility.getInteger(csvE.getItemAt(45))); 
                
                pstmt.executeUpdate();                
                pstmt.close();
                
                System.out.println("[ROW_INSERTED] ID -> " + csvE.getItemAt(0));
				
                totalCnt++;
			}
			
			conn.commit();
			
			System.out.println("\nTotal " + totalCnt + " row(s) inserted.");
			
		}catch(Exception e){
			
			try{
				conn.rollback();				
			}catch(Exception ee){
				System.out.println("[SQL_ROLLBACK EXCEPTION] -> \n");
				ee.printStackTrace();
			}
			
			System.out.println("[SQL_SUBDAO_EXCEPTION-ROLLBACK EXECUTED] -> \n");
			e.printStackTrace();
		}finally{			
			try{
				if(pstmt != null) pstmt.close();
			}catch(Exception ee){				
			}
			
		}
		
		// Connection close will be executed on Main DAO
		
	}
}